package com.cm309.hospital.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cm309.hospital.model.Doctor;

public class DoctorDao {
	public static MyDataSource mds = new MyDataSource();
	
	public List<Doctor> queryAll() {
		List<Doctor> doctors = new ArrayList<Doctor>();

		try {
			
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
//				ps = conn.prepareStatement("SELECT a.doctor_id,a.doctor_name,a.specialty,a.sex,b.subject_name FROM Doctor as a LEFT JOIN Subject b on b.subject_id = a.subject_id ");
				ps = conn.prepareStatement("SELECT a.doctor_id,a.doctor_name,a.sex,b.subject_name FROM doctor as a LEFT JOIN subject b on b.subject_id = a.subject_id ");
				ResultSet rs = ps.executeQuery();

				while (rs.next()) {
					Doctor c = new Doctor(rs.getString("doctor_id"),
							rs.getString("doctor_name"), 
							rs.getString("sex"),rs.getString("subject_name"));

					doctors.add(c);
				}

			} finally {
				ps.close();
			}
			return doctors;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	public Doctor querySingle(String doctor_id) {
		Doctor doctor = null;
		try {
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("SELECT a.doctor_id,a.doctor_name,a.sex,b.subject_name FROM Doctor as a LEFT JOIN Subject b on b.subject_id = a.subject_id WHERE a.doctor_id = ?");
				ps.setString(1, doctor_id);
				ResultSet rs = ps.executeQuery();

				if (rs.next()) {
					doctor = new Doctor(rs.getString("doctor_id"),
							rs.getString("doctor_name"), 
							rs.getString("sex"),rs.getString("subject_name"));
				}

			} finally {
				ps.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return doctor;
		
	}

	public List queryDoctorsBySubject(String subject_id) {
		List<Doctor> doctors = new ArrayList<Doctor>();
		try {
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("SELECT a.doctor_id,a.doctor_name,a.sex,b.subject_name FROM Doctor as a LEFT JOIN Subject b on b.subject_id = a.subject_id WHERE a.subject_id = ?");
				ps.setString(1, subject_id);
				ResultSet rs = ps.executeQuery();

				if (rs.next()) {
					Doctor doctor = null;
					doctor = new Doctor(rs.getString("doctor_id"),
							rs.getString("doctor_name"), 
							rs.getString("sex"),rs.getString("subject_name"));
					doctors.add(doctor);
				}

			} finally {
				ps.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return doctors;
		
	}
	
}
